let mysql = require('mysql');
const mysql_config = {
    host     : '127.0.0.1',
    user     : 'root',
    password : '',
    database : 'nodeapp'
};

class MySqlBase{
    constructor(options){
        this.connection = null;
        this.table_name = null;
        this.join_str = '';
        this.query_str = '';
        this.initSql();
        this.DateFormat();
    }
    initSql(){
        this.connection = mysql.createConnection(mysql_config);
    }
    add(params){
        this.connection.connect();
        let [columns, values] = this.handleParams(params);
        this.connection.query(`INSERT INTO ${this.table_name} ( ${columns}) VALUES (${values});`, function (err, rows, fields) {
            if (err) throw err;

            console.log(`${this.table_name} --table insert success`);
        }.bind(this));
        // this.connection.end();
    }
    del(id){
        this.connection.connect();
        this.connection.query(`DELETE FROM ${mysql_config.database}.${this.table_name} WHERE  id=${id};`, function (err, rows, fields) {
            if (err) throw err;

            console.log(`${this.table_name} --table delete success`);
        }.bind(this));
        // this.connection.end();
    }
    async search(params){
        let query_str = this.handleSearchParams(params);
        this.connection.connect((err, result) => {
            if (err) {
                console.log(err);
                console.log("连接失败");
                return;
            }
            console.log(result);
            console.log("连接成功");
        });
        let results = await new Promise(resolve =>
        {
            this.connection.query(`SELECT ${this.query_str||'*'} FROM ${this.table_name} ${this.join_str} ${query_str && 'WHERE '+ query_str }`,
                (err, rows, fields) => {
                    if (err) throw err;

                    resolve(rows)
                })
        })
        this.connection.end();
        return results.map(item => Object.assign(item, {
                    create_time: item.create_time instanceof Date && item.create_time.getTime(),
                    update_time: item.update_time instanceof Date && item.update_time.getTime()
                }
            )
        );
    }
    async update(params){
        let id = params.id;
        let query_str = this.handleUpdateParams(params);
        let results = await new Promise(resolve =>
        {
            this.connection.query(`UPDATE \`${this.table_name}\` SET ${query_str} WHERE  \`id\`=${id};`,
                (err, rows, fields) => {
                    if (err) throw err;

                    resolve(rows)
                })
        })
        this.connection.end();
        return results;
    }
    handleParams(params){
        let columns = [], values = [];
        columns = Object.keys(params).map(item => `\`${item}\``).join(',');
        values = Object.values(params).map(item => `'${item}'`).join(',');
        return [columns, values];
    }
    handleSearchParams(params){
        let query_str = [];
        for(let column in params){
            query_str.push(` ${this.table_name}.\`${column}\` = '${params[column]}' `)
        }
        return query_str.join('and');
    }
    handleUpdateParams(params){
        let query_str = [];
        delete params.id;
        for(let column in params){
            query_str.push(` \`${column}\` = '${params[column]}' `)
        }
        return query_str.join(',');
    }

    /**
     *
     * @param params
     * params.type 默认inner join , left为left join , right为right join
     * params.form.table_name 起始表
     * params.form.column 起始表关联字段
     * params.to.table_name 关联表
     * params.to.column 关联表关联字段
     * return join查询字符串 例 inner JOIN users ON users.id = article.user_id
     */
    join(params){
        let join_type = params.type || 'inner';
        params.from.table_name = params.from.table_name || this.table_name;
        this.join_str += ` ${join_type} JOIN ${params.to.table_name} ON ${params.to.table_name}.${params.to.column} = ${params.from.table_name}.${params.from.column}`
        return this;
    }
    query(query_str){
        this.query_str = query_str;
        return this
    }
    DateFormat(){
        Date.prototype.format = function(fmt) {
            var o = {
                "M+" : this.getMonth()+1,                 //月份
                "d+" : this.getDate(),                    //日
                "h+" : this.getHours(),                   //小时
                "m+" : this.getMinutes(),                 //分
                "s+" : this.getSeconds(),                 //秒
                "q+" : Math.floor((this.getMonth()+3)/3), //季度
                "S"  : this.getMilliseconds()             //毫秒
            };
            if(/(y+)/.test(fmt)) {
                fmt=fmt.replace(RegExp.$1, (this.getFullYear()+"").substr(4 - RegExp.$1.length));
            }
            for(var k in o) {
                if(new RegExp("("+ k +")").test(fmt)){
                    fmt = fmt.replace(RegExp.$1, (RegExp.$1.length==1) ? (o[k]) : (("00"+ o[k]).substr((""+ o[k]).length)));
                }
            }
            return fmt;
        };
    }
}


module.exports = MySqlBase;
